#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e

"create database if not exists edu_rpt;
use edu_rpt;



--每日意向人数变化曲线
drop table if exists hcx_rpt_day_cnt;
create table if not exists hcx_rpt_day_cnt(
    time_type string,
       create_date_time string,
       cus_amt bigint
 )   comment '每日意向人数变化曲线'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');

-- 每月地区用户访问量
drop table if exists hcx_rpt_month_area_cnt;
create table if not exists hcx_rpt_month_area_cnt(
       time_type  string,
       month_time string,
       cus_area_sheng string,
       group_type string,
       cus_amt bigint
 )   comment '每月地区意向用户'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');

-- 2013-3月份各大咨询中心获得的人数统计
drop table if exists hcx_rpt_month_chat_area_cnt;
create table if not exists hcx_rpt_month_chat_area_cnt(
   time_type string,
       month_time string,
       department_name string,
       group_type string,
       cus_amt bigint
 )   comment '2013-3月份各大咨询中心获得的人数统计'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');


--2015年的意向校区排行
drop table if exists kxh_rpt_year_school_cnt_top;
create table if not exists kxh_rpt_year_school_cnt_top(
       time_type string,
       year_time string,
       sch_name string,
       group_type string,
       cus_amt bigint
)comment '7月份通过来源渠道访问用户量排行榜'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');

drop table if exists hcx_rpt_year_sum_clue;
create table if not exists hcx_rpt_year_sum_clue(
    year string,
    year_sum bigint
 )   comment '总有效线索'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');"


